********************************************************************************
** 	TITLE: b11_1_alternative_delays.do
**
**	PROJECT: IGNITE
** 
**  PURPOSE: Cleans alternative measures of court case rescheduling instruments
**	(1) Holiday Delays in District Court
**	(2) County Financial Crises Delays in District Court
**	(3) Circuit Court Delays
**
**	Note: used for Table IV, Robustness Checks
********************************************************************************
		set sortseed 13
/********************************************************************************
**	(1) Holiday Delays in District Court
********************************************************************************/
	di "$date"
	
	use "$output_data/D67/ROA_for_merge.dta", clear
	replace case_id = subinstr(case_id," ","",.)
	duplicates drop case_id , force

	**** ROA - case number crosswalks (batches)
		preserve
		import excel "$input_data/dist_case_num/crosswalk.xlsx", clear firstrow
		
		ren querycase case_id
		replace case_id = upper(case_id)
		duplicates drop case_id, force 
		tempfile cw
		save `cw', replace
		
	restore
	
	preserve
	merge 1:1 case_id using `cw', nogen keep(3)
	tempfile fb_prep
	save `fb_prep', replace
	restore

	
	preserve
		import excel "$input_data/dist_case_num/dist_case_num_crosswalk_19jan2023.xlsx", clear firstrow
		tempfile cw_jms
		save `cw_jms', replace
		
	restore
	
	preserve
	use `fb_prep', clear
	merge 1:m dist_case_num using `cw_jms', nogen keep(1 3)
	tempfile fb
	save `fb',replace
	restore
	
	* second batch
	preserve
		import excel "$input_data/dist_case_num/crosswalk_08feb2023.xlsx", clear firstrow
		
		ren querycase case_id
		replace case_id = upper(case_id)
		duplicates drop case_id, force 
		tempfile cw2
		save `cw2', replace
		
	restore
	
	
	preserve
	merge 1:1 case_id using `cw2', nogen keep(3)
	merge 1:m case_number using "$input_data/dist_case_num/temp_missed_case_06feb2023.dta", ///
		nogen keep(1 3)
	tempfile sb
	save `sb'
	restore
	
	**third batch
	preserve
		import excel "$input_data/dist_case_num/crosswalk_08feb2023.xlsx", clear firstrow
		
		ren querycase case_id
		replace case_id = upper(case_id)
		replace case_id = subinstr(case_id,"-","0",.)
		duplicates drop case_id, force 
		tempfile cw3
		save `cw3', replace
		
	restore
	
	
	preserve
	merge 1:1 case_id using `cw3', nogen keep(3)
	
	merge 1:m case_number using "$input_data/dist_case_num/temp_missed_case_06feb2023.dta", ///
		nogen keep(3)
		
		tempfile tb
		save `tb', replace
	restore
	
	**fourth batch
	preserve
	use "$input_data/dist_case_num/criminal_recatch.dta", clear
	replace case_id = "20"+substr(case_id,1,2)+"-"+case_id+"-"+type
	tempfile cw4
	save `cw4', replace
	restore
	preserve
	merge 1:m case_id using `cw4', nogen keep(3)
		tempfile fourb
		save `fourb', replace
	restore
	
	**fifth batch
	preserve
	use "$input_data/dist_case_num/recatch_without_type.dta", clear
	append using "$input_data/dist_case_num/recatch_without_type_13mar2023.dta"
	ren case_id case_num
	duplicates drop case_num inmate booking_date, force
	tempfile cw5
	save `cw5', replace
	restore
	
	preserve
	cap drop case_num
	split case_id, p("-")
	ren case_id2 case_num
	duplicates drop case_num, force
	merge 1:m case_num using `cw5', nogen keep(3)
		tempfile fifthb
		save `fifthb', replace
	restore
	
	**sixth batch
	preserve
	use "$input_data/dist_case_num/recatch_with_type.dta", clear
	append using "$input_data/dist_case_num/recatch_with_type_13mar2023.dta"
	ren case_id case_num
	duplicates drop case_num inmate booking_date, force
	tempfile cw6
	save `cw6', replace
	restore
	
	preserve
	cap drop case_num
	split case_id, p("-")
	ren case_id2 case_num
	duplicates drop case_num, force
	merge 1:m case_num using `cw6', nogen keep(3)
		tempfile sixthb
		save `sixthb', replace
	restore
	
	****get case num for active inmates
	
	preserve 
	import delimited "$input_data/jms_all_22dec2022/gcsodefendentsbyjudge_excludingtether.csv", clear

	keep caseno1 group1
	split caseno1, p("-")

	foreach n in {
		replace caseno12 = "`n'" if strpos(caseno11,"`n'")>0
		replace caseno11 = subinstr(caseno11,"`n'","",.)

	}

	replace caseno11=upper(caseno11)
	replace caseno12=upper(caseno12)
	keep if !mi(caseno11)&!mi(caseno12)&mi(caseno13)

	drop if !strpos(caseno12,"SM")&!strpos(caseno12,"OM")&!strpos(caseno12,"FY")&!strpos(caseno12,"OT")&!strpos(caseno12,"ST")&!strpos(caseno12,"SD")     
	replace caseno11 = subinstr(caseno11,"T","T0",.) if length(caseno11)==7

	replace caseno11 = "22T00296" if caseno11 == "220296"

	replace caseno11 = "21T00991" if caseno1 == "21-0991FY"
	replace caseno12 = "FY" if  caseno1 == "21-0991FY"

	drop if length(caseno11)!=8

	ren group1 fullname
	ren caseno1 dist_case_num
	gen case_id = "20" + substr(caseno11,1,2) + "-" + caseno11 + "-" + caseno12

	replace fullname=proper(fullname)
	keep fullname case_id dist_case_num
	bys fullname: gen id =_n
		
	tempfile ct_case
	save `ct_case'
	restore
	
	preserve
	use "$output_data/001_merge_JMS_$date.dta",clear
	keep if D_current==1
	merge 1:m fullname using `ct_case', nogen keep(3)
	keep inmate booking_date case_id
	tempfile active_base
	save `active_base'
	restore
	
	preserve
	merge 1:m case_id using `active_base', nogen keep(3)
	tempfile active
	save `active'
	
	restore
	
	******append all
	clear 
	append using `fb', force
	append using `sb', force
	append using `tb', force
	append using `fourb', force
	append using `fifthb', force
	append using `sixthb', force
	append using `active', force
	duplicates drop inmate booking_date case_id, force
	
	preserve
	use "$output_data/001_merge_JMS_$date.dta", clear
	keep inmate booking_date release_reason1
	
	tempfile inmate_spell
	save `inmate_spell'
	restore
	merge m:1 inmate booking_date  using `inmate_spell', nogen keep(3) 
	bys inmate case_id (booking_date) : gen rb_same_charge = release_reason[_n-1] == "Bonded" &  strpos(lower(incarceration_type),"jail") 
	
	
	ren remove_event_date* remove*
	
//Holiday Dates	-- New Years, MLK Day, President's Day, Memorial Day, Independence Day, 
	//Labor Day, Columbus Day, Veteran's Day, Thanksgiving, and Christmas.
	local hli January/1/2016 ///
	January/18/2016 ///
	February/15/2016 ///
	May/30/2016 ///
	July/4/2016 ///
	September/5/2016 ///
	October/10/2016 ///
	November/11/2016 ///
	November/24/2016 ///
	December/25/2016 ///
	January/1/2017 ///
	January/2/2017 ///
	January/16/2017 ///
	February/20/2017 ///
	May/29/2017 ///
	July/4/2017 ///
	September/4/2017 ///
	October/9/2017 ///
	November/10/2017 ///
	November/11/2017 ///
	November/23/2017 ///
	December/25/2017 ///
	January/1/2018 ///
	January/15/2018 ///
	February/19/2018 ///
	May/28/2018 ///
	July/4/2018 ///
	September/3/2018 ///
	October/8/2018 ///
	November/11/2018 ///
	November/12/2018 ///
	November/22/2018 ///
	December/25/2018 ///
	January/1/2019 ///
	January/21/2019 ///
	February/18/2019 ///
	May/27/2019 ///
	July/4/2019 ///
	September/2/2019 ///
	October/14/2019 ///
	November/11/2019 ///
	November/28/2019 ///
	December/25/2019 ///
	January/1/2020 ///
	January/20/2020 ///
	February/17/2020 ///
	May/25/2020 ///
	July/3/2020 ///
	July/4/2020 ///
	September/7/2020 ///
	October/12/2020 ///
	November/11/2020 ///
	November/26/2020 ///
	December/25/2020 ///
	January/1/2021 ///
	January/18/2021 ///
	February/15/2021 ///
	May/31/2021 ///
	June/18/2021 ///
	June/19/2021 ///
	July/4/2021 ///
	July/5/2021 ///
	September/6/2021 ///
	October/11/2021 ///
	November/11/2021 ///
	November/25/2021 ///
	December/24/2021 ///
	December/25/2021 ///
	December/31/2021 ///
	January/1/2022 ///
	January/17/2022 ///
	February/21/2022 ///
	May/30/2022 ///
	June/19/2022 ///
	June/20/2022 ///
	July/4/2022 ///
	September/5/2022 ///
	October/10/2022 ///
	November/11/2022 ///
	November/24/2022 ///
	December/25/2022 ///
	December/26/2022 ///
	January/1/2023 ///
	January/2/2023 ///
	January/16/2023 ///
	February/20/2023 ///
	May/29/2023 ///
	June/19/2023 ///
	July/4/2023 ///
	September/4/2023 ///
	October/9/2023 ///
	November/10/2023 ///
	November/11/2023 ///
	November/23/2023 ///
	December/25/2023 

//Rescheduling Delay 14 days before and after holidays 	
		gen near_hld_14d = 0
	
	foreach n of varlist remove*{
		foreach holiday in `hli'{
			replace near_hld_14d = 1 if `n'<=date("`holiday'","MDY")+14&`n'>=date("`holiday'","MDY")-14
		}
	}
	
	
	keep inmate near_hld* booking_date
	collapse (max) near_hld*, by(inmate booking_date)
	save "$output_data/holiday_delays_2w.dta", replace
	
	
/********************************************************************************
**	(2) County Financial Crises Delays in District Court
********************************************************************************/	

	use "$output_data/D67/ROA_for_merge.dta", clear
	
	replace case_id = subinstr(case_id," ","",.)
	duplicates drop case_id , force
	
	//remove_event_date1-remove_event_date655
	keep case_id remove_event_date*
	tempfile rescheds
	save `rescheds', replace

	//Bring in the estimate sample from predicted recidivism measure data	
	use if e_sample using "$output_data/prediction.dta", clear

	keep inmate booking_date release_date case_id* return_3m_hat

	reshape long case_id, i(inmate booking_date) j(counts)
	keep if !missing(case_id)
	order inmate booking_date release_date, first
	
	
	merge m:1 case_id using `rescheds', nogen keep(3)
	

	*****Events: fiscal and closed crises			
		gen D_fiscal_resch=0
		gen D_closed_resch=0

	forval t = 1/655{
	format remove_event_date`t' %td
	
	replace D_fiscal_resch = 1 if (remove_event_date`t'>=date("06/01/2020","MDY")&remove_event_date`t'<date("08/01/2020","MDY"))
	
	**closure dummy

	replace D_closed_resch = 1 if (remove_event_date`t'>= date("11/10/2020","MDY") & remove_event_date`t'<=date("05/10/2021","MDY") | ///
		remove_event_date`t'>= date("03/17/2020","MDY") & remove_event_date`t'<=date("06/23/2020","MDY"))		
	}
	
	codebook D_fiscal_resch D_closed_resch
	
	collapse (max) D_fiscal_resch D_closed_resch (mean) return_3m_hat, by(inmate booking_date)
	
	codebook D_fiscal_resch D_closed_resch

	save "$output_data/crises_estimates_cases.dta", replace	
	
	
/********************************************************************************
**	(3) Circuit Court Delays
********************************************************************************/		
********************************************************************************
**  PURPOSE: To incorporate Circuit Court Delays for Robustness Checks
********************************************************************************

use "$output_data/estimate.dta", clear

* bring in circuit data

	keep inmate booking_date release_date circ_case_num* 
	sreshape long circ_case_num, i(inmate booking_date release_date) j(id) missing(drop all)

//Dropping non-circuit court case-numbers
	drop if strpos(lower(circ_case_num),"par")|strpos(lower(circ_case_num),"vio")| ///
		strpos(lower(circ_case_num),"out")|strpos(lower(circ_case_num),"fed")| ///
		strpos(lower(circ_case_num),"inm")|strpos(lower(circ_case_num),"dp")|  ///
		strpos(lower(circ_case_num),"ds")|strpos(lower(circ_case_num),"dm")|strpos(lower(circ_case_num),"fresh")
		
//Codes used in Circuit Court Criminal Cases (FH and FC)		
	keep if strpos(lower(circ_case_num),"fh")|strpos(lower(circ_case_num),"fc")
	replace circ_case_num=upper(circ_case_num)

//Cleaning circuit court case numbers
	cap drop cleaned_circ_id

	gen cleaned_circ_id = circ_case_num 
	replace cleaned_circ_id = subinstr(cleaned_circ_id," ","",.)
	replace cleaned_circ_id = subinstr(cleaned_circ_id,")","",.)
	replace cleaned_circ_id = subinstr(cleaned_circ_id,"(","",.)

	replace cleaned_circ_id  = subinstr(cleaned_circ_id,"FH","-FH",.) ///
		if strpos(cleaned_circ_id,"FH")&!strpos(cleaned_circ_id,"-FH")
	replace cleaned_circ_id  = subinstr(cleaned_circ_id,"FC","-FC",.) ///
		if strpos(cleaned_circ_id,"FC")&!strpos(cleaned_circ_id,"-FC")
	replace cleaned_circ_id  = substr(cleaned_circ_id,1,2) +"-"+substr(cleaned_circ_id,3,.) ///
		if substr(cleaned_circ_id,3,1)!="-"

	replace cleaned_circ_id = subinstr(cleaned_circ_id," ","",.)
	replace cleaned_circ_id = substr(cleaned_circ_id,1,12) ///
		if substr(cleaned_circ_id,-1,1)!="H"|substr(cleaned_circ_id,-1,1)!="C"
		
	replace cleaned_circ_id = substr(cleaned_circ_id,1,3) +"0"+substr(cleaned_circ_id,4,.) ///
		if length(cleaned_circ_id)==11
		
	replace cleaned_circ_id = substr(cleaned_circ_id,1,3) +"00"+substr(cleaned_circ_id,4,.) ///
		if length(cleaned_circ_id)==10
	replace cleaned_circ_id = substr(cleaned_circ_id,1,3) +"000"+substr(cleaned_circ_id,4,.) ///
		if length(cleaned_circ_id)==9

	unique inmate booking_date

//Merging Circuit Court case numbers to circuit Court ROA information	
		merge m:1 cleaned_circ_id using "$input_data/roas_circuit/circuit_ROAs_combined_07Feb2023.dta", nogen keep(3)
		keep inmate booking_date release_date cleaned_circ_id D_remove_any_circ event_type remove_reason circ_judge_name
		bys inmate booking_date release_date: gen id=_n
		sreshape wide D_remove_any_circ cleaned_circ_id event_type remove_reason circ_judge_name, i(inmate booking_date release_date) j(id)

//Generating Circuit Court Rescheduling Instruments		
		egen D_remove_any_circ=rowmax(D_remove_any_circ*)
			save "$output_data/circuit_delays_20240815.dta", replace	
	